const models = require('../db/mysqldb/index')
const { ErrorModel, SuccessModel, arrayToObject } = require('../utils/index')
const logger = require('../utils/logger')
const { PostgreSQL } = require('./../utils/Postgredb');
const { Oracle } = require('./../utils/Oracledb');
const { Query } = require("./../utils/query");
const { DataSqlModel } = require('../models/data_sql');
class DataSQLController {
  /**
   * @description 添加SQL
   * @param {*} name
   * @param {*} data_source_id
   * @param {*} code
   * @param {*} remark
   */
  static async dataSQLAdd (req, res, next) {
    const { name, data_source_id, code, remark, } = req.body
    const { user_id: op_user_id } = req.user
    logger.info(`DataSQLController`, `dataSQLAdd`, `req.body`, req.body)
    if (!name || !data_source_id) {
      res.json(new ErrorModel([], `缺少必填项`))
      return
    }
    try {
      const SQLInfo = await DataSqlModel.findByName(name);
      if (SQLInfo) {
        res.json(new ErrorModel([], `当前SQL已存在`))
        return
      }
      const result = await DataSqlModel.create(name, data_source_id, code, remark, op_user_id,)
      logger.info(`DataSQLController`, `dataSQLAdd`, `result`, result)
      res.json(new SuccessModel([], `新增SQL成功`))
    } catch (error) {
      logger.error(`DataSQLController`, `dataSQLAdd`, `error`, error)
      res.json(new ErrorModel([], error.message))
    }
  }
  /**
   * @description 更新SQL
   * @param {*} data_sql_id
   * @param {*} name
   * @param {*} data_source_id
   * @param {*} code
   * @param {*} remark
   */
  static async dataSQLUpd (req, res, next) {
    const {
      data_sql_id,
      name, data_source_id, code, remark,
    } = req.body
    const { user_id: op_user_id } = req.user
    logger.info(`DataSQLController`, `dataSQLUpd`, `req.body`, req.body)
    if (!data_sql_id || !name) {
      res.json(new ErrorModel([], `请输入必填项`))
      return
    }
    try {
      const SQLInfo = await DataSqlModel.findByNameAndId(name, data_sql_id)
      if (SQLInfo) {
        res.json(new ErrorModel([], `SQL已存在`))
        return
      }
      const result = await DataSqlModel.update(name, data_source_id, code, remark, op_user_id, data_sql_id)
      logger.info(`DataSQLController`, `dataSQLUpd`, `result`, result)
      res.json(new SuccessModel([], `SQL更新成功`))
    } catch (error) {
      logger.error(`DataSQLController`, `dataSQLUpd`, `error`, error)
      res.json(new ErrorModel([], error.message))
    }
  }
  /**
   * @description 删除SQL
   * @param {*} data_sql_id
   */
  static async dataSQLDel (req, res, next) {
    const { data_sql_id } = req.body
    const { user_id: op_user_id } = req.user
    logger.info(`DataSQLController`, `dataSQLDel`, `req.body`, req.body)
    if (!data_sql_id) {
      res.json(new ErrorModel([], `data_sql_id 获取失败`))
      return
    }
    try {
      const result = await DataSqlModel.delete(op_user_id, data_sql_id)
      logger.info(`DataSQLController`, `dataSQLDel`, `result`, result)
      res.json(new SuccessModel([], `删除SQL成功`))
    } catch (error) {
      logger.error(`DataSQLController`, `dataSQLDel`, `error`, error)
      res.json(new ErrorModel([], error.message))
    }
  }

  /**
   * @description SQL分页列表
   * @param {*} data_sql_id
   */
  static async dataSQLList (req, res, next) {
    const {
      limit,
      size = 1,
      name,
      data_source_id,
      remark,
    } = req.query
    logger.info(
      `DataSQLController`,
      `dataSQLList`,
      `req.query`,
      req.query
    )
    try {
      const { result, count } = await DataSqlModel.list(limit, size, name, data_source_id, remark,)
      logger.info(`DataSQLController`, `dataSQLList`, `result`, result, count)
      res.json(new SuccessModel({ result, total: count[0].count, limit: Number(limit), size: Number(size) }, `获取SQL数据成功`))
    } catch (error) {
      logger.error(`DataSQLController`, `dataSQLList`, `error`, error)
      res.json(new ErrorModel([], error.message))
    }
  }

  /**
   * @description SQL总列表
   */
  static async dataSQLAllList (req, res, next) {
    try {
      const result = await DataSqlModel.allList()
      logger.info(`DataSQLController`, `dataSQLAllList`, `result`, result,)
      res.json(new SuccessModel({ result, }, `获取SQL数据成功`))
    } catch (error) {
      logger.error(`DataSQLController`, `dataSQLAllList`, `error`, error)
      res.json(new ErrorModel([], error.message))
    }
  }
  /**
   * @description 测试SQL
   * @param {*} name
   * @param {*} data_sql_id
   * @param {*} code
   */
  static async dataSQLCheck (req, res, next) {
    const { data_source_id, code, } = req.body
    logger.info(`DataSQLController`, `dataSQLCheck`, `req.body`, req.body)
    if (!data_source_id || !code) {
      res.json(new ErrorModel([], `缺少必填项`))
      return
    }
    try {
      const sourceInfo = await models.data_source.findOne({
        where: {
          data_source_id
        }
      })
      if (!sourceInfo) {
        res.json(new ErrorModel([], `查询失败`))
      }
      switch (sourceInfo.db_type) {
        case "MySQL":
          const mysqlInfo = {
            host: sourceInfo.host,
            port: sourceInfo.port,                 // 连接端口
            user: sourceInfo.user,
            password: sourceInfo.password,
            database: sourceInfo.db_name,
          }
          Query.getMySQLData(mysqlInfo, code).then(async (rows) => {
            logger.info(`DataSQLController`, `dataSQLCheck`, rows)
            res.json(new SuccessModel(rows, `success`))
          }).catch(async (err) => {
            logger.error(`DataSQLController`, `dataSQLCheck`, `error`, err)
            res.json(new ErrorModel([], err.message))
          })
          break;
        case "PostgreSQL":
          const pgInfo = {
            host: sourceInfo.host,        // 数据库所在IP
            user: sourceInfo.user,           // 数据库用户名
            database: sourceInfo.db_name,       // 数据库
            password: sourceInfo.password,       // 数据库密码
            port: sourceInfo.port                 // 连接端口
          };
          PostgreSQL.connect(pgInfo).then((pgConfig) => {
            Query.getPostgreSQLData(pgConfig, code).then(async (rows) => {
              logger.info(`DataSQLController`, `dataSQLCheck`, rows)
              await PostgreSQL.close(pgConfig);
              res.json(new SuccessModel(rows, `success`))
            }).catch(async (err) => {
              logger.error(`DataSQLController`, `dataSQLCheck`, `error`, err)
              await PostgreSQL.close(pgConfig);
              res.json(new ErrorModel([], err.message))
            })
          }).catch(err => {
            logger.error(`DataSQLController`, `dataSQLCheck`, `error`, err)
            res.json(new ErrorModel([], err.message))
          })
          break;
        case "Oracle":
          const oracleInfo = {
            user: sourceInfo.user,　　//用户名
            password: sourceInfo.password,　　//密码
            connectString: `${sourceInfo.host}:${sourceInfo.port}/${sourceInfo.db_name}`,
          };
          Oracle.connect(oracleInfo).then((oracleConfig) => {
            Query.getOracleData(oracleConfig, code).then(async (rows) => {
              logger.info(`DataSQLController`, `dataSQLCheck`, rows)
              await Oracle.close(oracleConfig);
              res.json(new SuccessModel(rows, `success`))
            }).catch(async (err) => {
              logger.error(`DataSQLController`, `dataSQLCheck`, `error`, err)
              await Oracle.close(oracleConfig);
              res.json(new ErrorModel([], err.message))
            })
          }).catch(err => {
            logger.error(`DataSQLController`, `dataSQLCheck`, `error`, err)
            res.json(new ErrorModel([], err.message))
          })
          break;
        default:
          res.json(new ErrorModel([], "暂不支持此类型数据库"))
          break;
      }
    } catch (error) {
      logger.error(`DataSQLController`, `dataSQLCheck`, `error`, error)
      res.json(new ErrorModel([], error.message))
    }
  }
  /**
   * @description 获取SQL数据
   * @param {*} data_sql_id
   */
  static async dataSQLByID (req, res, next) {
    const { data_sql_id } = req.body
    logger.info(`DataSQLController`, `dataSQLByID`, `req.body`, req.body)
    if (!data_sql_id) {
      res.json(new ErrorModel([], `查询失败,获取不到data_sql_id`))
      return
    }
    try {
      const sourceInfo = await DataSqlModel.sqlDetail(data_sql_id)
      if (!sourceInfo) {
        logger.info(`DataSQLController`, `dataSQLByID`, "sourceInfo", sourceInfo)
        res.json(new ErrorModel([], "查询失败"))
      }
      const { db_type, host, port, user, password, db_name, code } = sourceInfo[0]
      switch (db_type) {
        case "MySQL":
          const mysqlInfo = {
            host: host,
            port: port,                 // 连接端口
            user: user,
            password: password,
            database: db_name,
          }
          Query.getMySQLData(mysqlInfo, code).then(async (rows) => {
            logger.info(`DataSQLController`, `dataSQLCheck`, rows)
            res.json(new SuccessModel(rows, `success`))
          }).catch(async (err) => {
            logger.error(`DataSQLController`, `dataSQLCheck`, `error`, err)
            res.json(new ErrorModel([], err.message))
          })
          break;
        case "PostgreSQL":
          const pgInfo = {
            host: host,        // 数据库所在IP
            user: user,           // 数据库用户名
            database: db_name,       // 数据库
            password: password,       // 数据库密码
            port: port                 // 连接端口
          };
          PostgreSQL.connect(pgInfo).then((pgConfig) => {
            Query.getPostgreSQLData(pgConfig, code).then(async (rows) => {
              logger.info(`DataSQLController`, `dataSQLByID`, rows)
              await PostgreSQL.close(pgConfig);
              res.json(new SuccessModel(rows, `success`))
            }).catch(async (err) => {
              logger.error(`DataSQLController`, `dataSQLByID`, `error`, err)
              await PostgreSQL.close(pgConfig);
              res.json(new ErrorModel([], err.message))
            })
          }).catch(err => {
            logger.error(`DataSQLController`, `dataSQLByID`, `error`, err)
            res.json(new ErrorModel([], err.message))
          })
          break;
        case "Oracle":
          const oracleInfo = {
            user: user,　　//用户名
            password: password,　　//密码
            connectString: `${host}:${port}/${db_name}`,
          };
          Oracle.connect(oracleInfo).then((oracleConfig) => {
            Query.getOracleData(oracleConfig, code).then(async (rows) => {
              logger.info(`DataSQLController`, `dataSQLByID`, rows)
              await Oracle.close(oracleConfig);
              res.json(new SuccessModel(rows, `success`))
            }).catch(async (err) => {
              logger.error(`DataSQLController`, `dataSQLByID`, `error`, err)
              await Oracle.close(oracleConfig);
              res.json(new ErrorModel([], err.message))
            })
          }).catch(err => {
            logger.error(`DataSQLController`, `dataSQLByID`, `error`, err)
            res.json(new ErrorModel([], err.message))
          })
          break;
        default:
          res.json(new ErrorModel([], "暂不支持此类型数据库"))
          break;
      }
    } catch (error) {
      logger.error(`DataSQLController`, `dataSQLByID`, `error`, error)
      res.json(new ErrorModel([], error.message))
    }
  }
}

module.exports = {
  DataSQLController,
}
